package com.lzq.mybatis.demo.mapper;

import com.lzq.mybatis.demo.model.ArticleInfo;
import com.lzq.mybatis.demo.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserInfoMapper {
    @Insert("insert into userinfo(username,password,age,gender,phone)" +
            "values ('zhaoliu','zhaoliu',19,1,'18700001234')")
    public void insert();

    @Insert("insert into userinfo(username,password,age,gender,phone)" +
            "values (#{username},#{password},#{age},#{gender},#{phone})")
    public void insert2(UserInfo userInfo);

    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into userinfo(username,password,age,gender,phone)" +
            "values (#{userInfo.username},#{userInfo.password},#{userInfo.age},#{userInfo.gender},#{userInfo.phone})")
    public void insert3(@Param("userInfo") UserInfo userInfo);

    @Delete("delete from userinfo where id = #{id}")
    public void delete(Integer id);

    @Update("update userinfo set username = #{userInfo.username} where age = #{userInfo.age}")
    public void update(@Param("userInfo") UserInfo userInfo);

    @Select("select * from userinfo")
    public List<UserInfo> queryAllUserInfo();

    @Select("select * from userinfo where id = 3")
    public List<UserInfo> queryUserInfoById();

    @Select("select * from userinfo where id = #{id}")//预编译sql
    public List<UserInfo> queryUserInfoById2(@Param("id") Integer id);

    @Select("select * from userinfo where id = ${id}")//即时sql
    public List<UserInfo> queryUserInfoById3(@Param("id") Integer id);

    @Select("select * from userinfo where username = #{username}")//自动加''
    public List<UserInfo> queryByName1(String username);

    @Select("select * from userinfo where username = ${username}")//不加'',可能会报错
    public List<UserInfo> queryByName2(String username);

    //1.起别名
    @Select("select id, username, password, age, gender, phone, delete_flag as deleteFlag," +
            "create_time as createTime, update_time as updateTime from userinfo")
    public List<UserInfo> queryAllUser();

    //2.结果映射
    @Results({
        @Result(column = "delete_flag",property = "deleteFlag"),
        @Result(column = "delete_flag",property = "deleteFlag"),
        @Result(column = "delete_flag",property = "deleteFlag")
    })
    @Select("select id, username, password, age, gender, phone, delete_flag " +
            "create_time,update_time from userinfo")
    public List<UserInfo> queryAllUser2();

    @Select("select id, username, password, age, gender, phone, delete_flag " +
            "create_time,update_time from userinfo")
    public List<UserInfo> queryAllUser3();

    @Select("select\n" +
            "ta.id,\n" +
            "ta.title,\n" +
            "ta.content,\n" +
            "ta.uid,\n" +
            "tb.username,\n" +
            "tb.age,\n" +
            "tb.gender\n" +
            "FROM\n" +
            "articleinfo ta\n" +
            "LEFT JOIN userinfo tb ON ta.uid = tb.id\n" +
            "WHERE\n" +
            "ta.id =#{id}")
    public ArticleInfo queryAllById2(Integer id);
}
